package excel

import (
	"JiXiaoCalc/log"
	"JiXiaoCalc/model"
	"fmt"
	"github.com/xuri/excelize/v2"
	"strconv"
)

var excelInfo model.ExcelInfo

func OpenFile(file string) (*excelize.File, error) {
	f, err := excelize.OpenFile(file)
	if err != nil {
		log.Logger().Error("打开文件失败", err)
		return nil, err
	}
	return f, nil
}

func GetRows(f *excelize.File) ([][]string, error) {
	log.Logger().Info("开始获取Excel工作表数据")
	sheets := f.GetSheetList()
	log.Logger().Infof("获取到的工作表列表长度：%d，内容：%v", len(sheets), sheets)
	if len(sheets) == 0 {
		errMsg := "工作表为空"
		log.Logger().Error(errMsg)
		return nil, fmt.Errorf(errMsg)
	}
	rows, err := f.GetRows(sheets[0])
	if err != nil {
		log.Logger().Error("获取行数据失败", err)
		return nil, fmt.Errorf("获取行数据失败:%s", err)
	}

	if len(rows) == 0 {
		errMsg := "工作表无数据行（无表头和内容）"
		log.Logger().Error(errMsg)
		return nil, fmt.Errorf(errMsg)
	}
	// 解析表头（此时 rows[0] 一定存在）
	if err = findColumnIndices(rows[0]); err != nil {
		return nil, err
	}
	// 仅有表头，无实际数据行
	if len(rows) == 1 {
		errMsg := "表格仅有表头，无实际数据行"
		log.Logger().Error(errMsg)
		return nil, fmt.Errorf(errMsg)
	}
	return rows, nil
}

func GetExcelInfo() *model.ExcelInfo {
	return &excelInfo
}

// findColumnIndices 从表头行查找关键列索引
func findColumnIndices(headerRow []string) error {
	for colIndex, cellValue := range headerRow {
		switch cellValue {
		case "系统情况":
			excelInfo.SystemInfoIndex = colIndex
			break
		case "系统总数":
			excelInfo.SystemTotalIndex = colIndex
			break
		case "绩效比例":
			excelInfo.PerformanceRatioIndex = colIndex
			break
		case "人员汇总":
			excelInfo.PersonnelSummaryIndex = colIndex
			break
		case "子系统总数":
			excelInfo.SubsystemTotalIndex = colIndex
			break
		case "基点数量":
			excelInfo.BasePointsIndex = colIndex
			break
		case "项目基数":
			excelInfo.ProjectBaseIndex = colIndex
			break
		case "折扣比例":
			excelInfo.ZheKouBiLiIndex = colIndex
			break
		case "申请金额":
			excelInfo.ApplicationAmountIndex = colIndex
			break
		case "最终金额":
			excelInfo.FinalAmountIndex = colIndex
			break
		default:
			break
		}
	}
	// 检查必填列
	if excelInfo.SystemInfoIndex < 0 || excelInfo.SystemTotalIndex < 0 ||
		excelInfo.PerformanceRatioIndex < 0 || excelInfo.PersonnelSummaryIndex < 0 ||
		excelInfo.SubsystemTotalIndex < 0 || excelInfo.BasePointsIndex < 0 ||
		excelInfo.ProjectBaseIndex < 0 || excelInfo.ZheKouBiLiIndex < 0 ||
		excelInfo.ApplicationAmountIndex < 0 || excelInfo.FinalAmountIndex < 0 {
		return fmt.Errorf("部分关键列未找到，请检查表头")
	}
	return nil
}

func WriteNewColumnData(f *excelize.File, data *model.RowData, rowIndex int) {
	sheetName := f.GetSheetName(0)
	// 系统总数
	_ = f.SetCellValue(sheetName, cellIndexToAlpha(excelInfo.SystemTotalIndex, rowIndex), data.SystemTotal)
	// 子系统总数
	_ = f.SetCellValue(sheetName, cellIndexToAlpha(excelInfo.SubsystemTotalIndex, rowIndex), data.SubsystemTotal)
	// 基点数量
	_ = f.SetCellValue(sheetName, cellIndexToAlpha(excelInfo.BasePointsIndex, rowIndex), data.BasePoints)
	// 项目基数
	_ = f.SetCellValue(sheetName, cellIndexToAlpha(excelInfo.ProjectBaseIndex, rowIndex), data.ProjectBase)
	// 折扣比例
	_ = f.SetCellValue(sheetName, cellIndexToAlpha(excelInfo.ZheKouBiLiIndex, rowIndex), data.ZheKouBiLi)
	// 申请金额
	_ = f.SetCellValue(sheetName, cellIndexToAlpha(excelInfo.ApplicationAmountIndex, rowIndex), data.ApplicationAmount)
	// 最终金额
	_ = f.SetCellValue(sheetName, cellIndexToAlpha(excelInfo.FinalAmountIndex, rowIndex), data.FinalAmount)
}

// AllocateAmountToPersons 分配最终金额到人名列
func AllocateAmountToPersons(f *excelize.File, rowIndex int, finalAmount float64, row []string) {
	// 1. 计算总比例
	totalRatio := 0.0
	var temp [][]float64
	for colIndex := excelInfo.PersonnelSummaryIndex; colIndex < len(row); colIndex++ {
		if ratioVal, err := strconv.ParseFloat(row[colIndex], 64); err == nil && ratioVal > 0 {
			temp = append(temp, []float64{float64(colIndex), ratioVal})
			totalRatio += ratioVal
		}
	}

	if totalRatio != 1 {
		log.Logger().Errorf("第 %d 行：人名列总比例不为100%%，跳过分配\n", rowIndex+1)
		//fmt.Printf("第 %d 行：人名列总比例不为100%%，跳过分配\n", rowIndex+1)
		return
	}

	// 计算金额并格式化
	for _, value := range temp {
		amount := finalAmount * value[1]
		percentage := fmt.Sprintf("%.0f%%", value[1]*100)
		cellValue := fmt.Sprintf("%.2f（%s）", amount, percentage)
		_ = f.SetCellValue(f.GetSheetName(0), cellIndexToAlpha(int(value[0]), rowIndex+1), cellValue)
	}
	return
}

// cellIndexToAlpha 该函数主要实现将将数字索引转换为 Excel 表格中的列字母标识，比如将1转换为A
func cellIndexToAlpha(colIndex, rowIndex int) string {
	var result string
	colIndex++
	for colIndex > 0 {
		colIndex--
		result = string('A'+colIndex%26) + result
		colIndex /= 26
	}
	return result + strconv.Itoa(rowIndex)
}
